﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using vgo_vs01.Models.KetNoi;
using vgo_vs01.Models.DoiTuong;

namespace vgo_vs01.Models
{
    public class ThanhVienModels
    {
        #region Hàm trả về tenDangNhap
        //Hàm test trả về userName
        public string GetUserName(string TenDangNhap)
        {
            ConnectDB objData = new ConnectDB();
            string strSQL = "";
            ThanhVien thanhVien;
            try
            {
                strSQL = "sp_kiemTraTenDangNhapTonTai @TenDangNhap = N'" + TenDangNhap + "'";
                objData.ExeReader(strSQL);
                if (objData.dataRead.Read())
                {
                    thanhVien = new ThanhVien();
                    thanhVien.TenDangNhap = objData.dataRead["TenDangNhap"].ToString();
                    if (thanhVien.TenDangNhap == TenDangNhap)
                    {
                        return TenDangNhap;
                    }
                }
            }
            catch (Exception ex)
            {
                Console.Write("Error: " + ex);
            }
            finally
            {
                objData.CloseData();
                objData = null;
            }
            return TenDangNhap;
        }
        #endregion
        //Hàm kiểm tra tên đăng nhập
        #region kiem tra tenDangNhap
        //Hàm test trả về userName
        public bool isExistUserName(string TenDangNhap)
        {
            bool daTonTai = false;
            ConnectDB objData = new ConnectDB();
            string strSQL = "";
            ThanhVien thanhVien;
            try
            {
                strSQL = "sp_kiemTraTenDangNhapTonTai @TenDangNhap = N'" + TenDangNhap + "'";
                objData.ExeReader(strSQL);
                if (objData.dataRead.Read())
                {
                    thanhVien = new ThanhVien();
                    thanhVien.TenDangNhap = objData.dataRead["TenDangNhap"].ToString();
                    if (thanhVien.TenDangNhap == TenDangNhap)
                    {
                        daTonTai = true;
                    }
                    else
                        daTonTai = false;
                }
            }
            catch (Exception ex)
            {
                Console.Write("Error: " + ex);
            }
            finally
            {
                objData.CloseData();
                objData = null;
            }
            return daTonTai;
        }
        #endregion
       //Hàm kiểm tra Email
        #region Hàm kiểm tra Email
        public bool isExistEmail(string Email) 
        {
            bool daTonTai = false;
            ConnectDB objData = new ConnectDB();
            string strSQL = "";
            ThanhVien thanhVien;
            try
            {
                strSQL = "sp_kiemTraEmailTonTai @Email = N'" + Email + "'";
                objData.ExeReader(strSQL);
                if (objData.dataRead.Read())
                {
                    thanhVien = new ThanhVien();
                    thanhVien.Email = objData.dataRead["Email"].ToString();
                    if (thanhVien.Email == Email)
                    {
                        daTonTai = true;
                    }
                    else
                        daTonTai = false;
                }
            }
            catch (Exception ex)
            {
                Console.Write("Error: " + ex);
            }
            finally
            {
                objData.CloseData();
                objData = null;
            }
            return daTonTai;
        }
        #endregion
        //Hàm kiểm tra CMT
        #region Hàm kiểm tra CMND
        public bool KiemTraCMT(string CMND) 
        {
            bool daTonTai = false;
            ConnectDB objData = new ConnectDB();
            string strSQL = "";
            ThanhVien thanhVien;
            try
            {
                strSQL = "sp_kiemTraTontaiCMT @CMND = N'" + CMND + "'";
                objData.ExeReader(strSQL);
                if (objData.dataRead.Read())
                {
                    thanhVien = new ThanhVien();
                    thanhVien.CMND = objData.dataRead["CMND"].ToString();
                    if (thanhVien.CMND == CMND)
                    {
                        daTonTai = true;
                    }
                    else
                        daTonTai = false;
                }
            }
            catch (Exception ex)
            {
                Console.Write("Error: " + ex);
            }
            finally
            {
                objData.CloseData();
                objData = null;
            }
            return daTonTai;
        }
        #endregion
        //Hàm Kiểm tra sdt
        #region hàm kiểm tra số điện thoại
        public bool isExistPhonNumber(string SDT) 
        {
            bool daTonTai = false;
            ConnectDB objData = new ConnectDB();
            string strSQL = "";
            ThanhVien thanhVien;
            try
            {
                strSQL = "sp_kiemTraTonTaiSDT @SDT = N'" + SDT + "'";
                objData.ExeReader(strSQL);
                if (objData.dataRead.Read())
                {
                    thanhVien = new ThanhVien();
                    thanhVien.SDT = objData.dataRead["SDT"].ToString();
                    if (thanhVien.SDT == SDT)
                    {
                        daTonTai = true;
                    }
                    else
                        daTonTai = false;
                }
            }
            catch (Exception ex)
            {
                Console.Write("Error: " + ex);
            }
            finally
            {
                objData.CloseData();
                objData = null;
            }
            return daTonTai;   
        }
        #endregion
        //Hàm Đăng ký
        #region Hàm Đăng ký thành viên
        public void DangKyThanhVien(ThanhVien thanhVien) 
        {
            ConnectDB objConnect = new ConnectDB();
            thanhVien.MaQuyen = 1;
            string strSQL = "";
            try
            {
                strSQL = "sp_themThanhVien @HoTen = N'" + thanhVien.HoTen +"'"
                                         +",@NgaySinh ='"+ thanhVien.NgaySinh +"'"
                                         +",@CMND = '"+ thanhVien.CMND +"'"
                                         +",@NgayCap ='" + thanhVien.NgayCapCMT + "'"
                                         +",@NoiCap= N'"+ thanhVien.NoiCapCMT +"'"
                                         +",@TenDangNhap =N'"+thanhVien.TenDangNhap +"'"
                                         +",@MatKhau = N'"+thanhVien.MatKhau+"'"
                                         +",@QueQuan =N'"+thanhVien.QueQuan +"'"
                                         +",@DiaChi =N'"+thanhVien.DiaChi +"'"
                                         +",@SDT ='"+thanhVien.SDT+"'"
                                         +",@Email =N'"+thanhVien.Email+"'"
                                         +",@MaQuyen = "+thanhVien.MaQuyen +""
                    ;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
            finally
            {
                objConnect.CloseData();
                objConnect = null;
            }
        }
        #endregion
        #region Hàm kiểm tra mật khẩu
        public bool KTMatKhau(string MatKhau, string tendangnhap)
        {
            bool daTonTai = false;
            ConnectDB objData = new ConnectDB();
            string strSQL = "";
            ThanhVien thanhVien;
            try
            {
                strSQL = "sp_KTMatKhau @matkhau = N'" + MatKhau + "'" + ",@tendangnhap=N'" + tendangnhap + "'";
                objData.ExeReader(strSQL);
                if (objData.dataRead.Read())
                {
                    thanhVien = new ThanhVien();
                    thanhVien.MatKhau = objData.dataRead["MatKhau"].ToString();
                    if (thanhVien.MatKhau == MatKhau)
                    {
                        daTonTai = true;
                    }
                    else
                        daTonTai = false;
                }
            }
            catch (Exception ex)
            {
                Console.Write("Error: " + ex);
            }
            finally
            {
                objData.CloseData();
                objData = null;
            }
            return daTonTai;
        }
        #endregion
        public void DoiMatKhau( ThanhVien tv)
        {
            ConnectDB con = new ConnectDB();
            try
            {
                string sql = "sp_Doimatkhau @matkhau=N'" + tv.MatKhau + "'" + ",@tendangnhap=N'" + tv.TenDangNhap + "'";
                con.ExeNonQuery(sql);
            }
            catch (Exception ex)
            {
                Console.Write(ex);
            }
            finally
            {
                con.CloseData();
                con = null;
            }
         
        }
        public void SuaThongTin(ThanhVien tv)
        {
            ConnectDB con = new ConnectDB();
            try
            {
                string sql = "sp_thanhviensuathongtin @hoten=N'"+tv.HoTen+"'"+",@ngaysinh='"+tv.NgaySinh+"'"+",@CMND='"+tv.CMND+"'"+",@Email='"+tv.Email+"'"+",@SDT='"+tv.SDT+"'"+",@diachi=N'"+tv.DiaChi+"'"+",@QueQuan=N'"+tv.QueQuan+"'"+",@Noicap=N'"+tv.NoiCapCMT+"'"+",@NgayCap='"+tv.NgayCapCMT+"'";
             
                con.ExeNonQuery(sql);
            }
            catch (Exception ex)
            {
                Console.Write(ex);
            }
            finally 
            {
                con.CloseData();
                con = null;
            }
           
        }
        public void XoaThanhvien(int MaThanhVien)
        {
              ConnectDB con = new ConnectDB();
            try
            {
             
                string sql = "sp_xoathanhvien @MaThanhvien='" + MaThanhVien + "'";
                con.ExeNonQuery(sql);
            }
            catch (Exception ex)
            { Console.Write(ex); }
            finally { con.CloseData();
            con = null;
            }
        }
        public void LayMaThanhVien(int MaThanhVien)
        {
            string sql="sp_LayMaThanhVien @MaThanhVien='"+MaThanhVien+"'"l
        }
        public List<ThanhVien> laydstv()
        {
               
                List<ThanhVien> dstv = new List<ThanhVien>();
                string sql = "select*from Thanhvien";
                ConnectDB con = new ConnectDB();
                con.ExeReader(sql);
                while (con.dataRead.Read())
                {
                    ThanhVien tv = new ThanhVien();
                    tv.MaThanhVien = int.Parse(con.dataRead["MaThanhVien"].ToString());
                    tv.HoTen = con.dataRead["HoTen"].ToString();
                    dstv.Add(tv);

                }
          
            return dstv;
        }
    }
}